package com.btpn.custody.bean;

import it.sauronsoftware.cron4j.Scheduler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import com.btpn.custody.common.Constant;
import com.btpn.custody.common.LogEvent;
import com.btpn.custody.common.Query;

@Component
public class SchedulerBeans {

	private final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	private DataSource CustodyDataSource;
	
	@Autowired
	private DataSource DwhDataSource;
	
	@Autowired
	private DailyTask dailyTask;
	
	@Autowired
	private ParameterBeans parameterBeans;
	
	@Value("${SCHEDULER_DOWNLOAD_FROM_DWH}")
	private String schedulerPattern;
	
	/*
	 * PURPOSE
	 * - refresh table parameter (if still null)
	 * - download data (daily)
	 */
	@PostConstruct
	public void initScheduler() {
		
		refreshDownloadParameter(Constant.USER_SYSTEM);
		
		// Creates a Scheduler instance (i use cron4j)
		Scheduler s = new Scheduler();
		s.schedule(schedulerPattern, new Runnable() {
			
			public void run() {				
				// cek first initial
//				if (isNeedInitial()) {
//					firstInitial(Constant.USER_SYSTEM);
//				} else {
				logger.info("TRY AT DAILY ===> Is in progress:{}", parameterBeans.isInProgress());
				if (!parameterBeans.isInProgress()) {
					parameterBeans.setInProgress(true);
					dailyTask.dailyInitial(Constant.USER_SYSTEM);
				}
//				}
			}
		});		
		s.start();
	}

	/*
	 * PURPOSE : cek apakah table parameter di CMT masih kosong
	 * RETURN count
	 */
	public Integer getParameterCount() {
		Connection cmtConn = null;
		Statement stmt = null;
		int amount = 0;
		try {
			cmtConn = CustodyDataSource.getConnection();
			
			stmt = cmtConn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS amount FROM " + Constant.CMT_M_PARAM);
			while (rs.next()) {
				amount = rs.getInt("amount");
			}
			
		} catch (SQLException e) {
			logger.error(LogEvent.GENERAL, e);
		} finally {
			if (stmt!=null) {
				try {
					stmt.close();stmt=null;
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();cmtConn=null;
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
		
		return amount;
	}
	
	/*
	 * PURPOSE : refresh table parameter (CMT.TBL_M_PARAM)
	 * Note : refresh by admin manualy OR at first time
	 * warning : it will reset SLA param
	 */
	public Boolean refreshDownloadParameter(String user) {
		
		if (copyDownloadParameter(user)) {		
			// wire in spring
			return parameterBeans.initialParameter(user);			
		} else {
			return false;
		}
	}
	
	/*
	 * PURPOSE : copy to table parameter (CMT.TBL_M_PARAM)
	 */
	public Boolean copyDownloadParameter(String user) {
		logger.info("<===== START PARAMETER DOWNLOAD =====>");
	
		int c_param=0, c_country=0, c_cst_type=0, c_acc_type=0;
		Connection dwhConn = null;
		Connection cmtConn = null;
		PreparedStatement ps_cmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			dwhConn = DwhDataSource.getConnection();
			cmtConn = CustodyDataSource.getConnection();
			
			// Clear all parameter			
			stmt = cmtConn.createStatement();
			stmt.executeUpdate("TRUNCATE TABLE " + Constant.CMT_M_PARAM);
			
			// I'm separate it because it come from different view :
			ps_cmt = cmtConn.prepareStatement("INSERT INTO " + Constant.CMT_M_PARAM + " VALUES (?,?,?)");
			
			// 1. Copy from DWH.VI_CMT_M_PARAM to CMT.TBL_M_PARAM
			String query = "SELECT p.ITEM_NAME, p.VALUE_CODE, p.VALUE_DESC FROM " + Constant.DWH_VW_PARAM + " p WHERE p.ITEM_NAME IN ('" +
					Constant.P0102 + "','" + 
					Constant.P0108 + "','" + 
					Constant.P0109 + "','" + 
					Constant.P0110 + "','" + 
					Constant.P0111 + "','" + 
					Constant.P0112 + "','" + 
					Constant.P0118 + "','" + 
					Constant.P0124 + "','" + 
					Constant.P0137 + "','" + 
					Constant.P0140 + "','" +
					
					Constant.P0141 + "','" + 
					Constant.P0142 + "','" + 
					Constant.P0143 + "','" + 
					Constant.P0144 + "','" + 
					Constant.P0145 + "','" + 
					Constant.P0146 + "')";
			
			stmt = dwhConn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				ps_cmt.setString(1, StringUtils.trimToEmpty(rs.getString("ITEM_NAME")));
				ps_cmt.setString(2, StringUtils.trimToEmpty(rs.getString("VALUE_CODE")));
				ps_cmt.setString(3, StringUtils.deleteWhitespace(rs.getString("VALUE_DESC")));
				ps_cmt.executeUpdate();
				c_param++;
			}

			// 2. Copy from DWH.VI_COUNTRY to CMT.TBL_M_PARAM			
			rs = stmt.executeQuery(Query.SELECT__VI_COUNTRY);
			while (rs.next()) {
				ps_cmt.setString(1, Constant.P0001);
				ps_cmt.setString(2, StringUtils.trimToEmpty(rs.getString("COUNTRY_CODE")));
				ps_cmt.setString(3, StringUtils.deleteWhitespace(rs.getString("COUNTRY_NAME")));
				ps_cmt.executeUpdate();
				c_country++;
			}

			// 3. Copy from DWH.VI_CMT_M_CUSTOMER_TYPE to CMT.TBL_M_PARAM
			rs = stmt.executeQuery(Query.SELECT__VI_CST_TYPE);
			while (rs.next()) {
				ps_cmt.setString(1, Constant.P0002);
				ps_cmt.setString(2, StringUtils.trimToEmpty(rs.getString("CUSTOMER_TYPE")));
				ps_cmt.setString(3, StringUtils.deleteWhitespace(rs.getString("CUSTOMER_DESCR")));
				ps_cmt.executeUpdate();
				c_cst_type++;
			}

			// 4. Copy from DWH.VI_CMT_M_ACCOUNT_TYPE to CMT.TBL_M_PARAM
			rs = stmt.executeQuery(Query.SELECT__VI_ACC_TYPE);
			while (rs.next()) {
				ps_cmt.setString(1, Constant.P0003);
				ps_cmt.setString(2, StringUtils.trimToEmpty(rs.getString("ACCOUNT_TYPE")));
				ps_cmt.setString(3, StringUtils.deleteWhitespace(rs.getString("ACCOUNT_DESCR")));
				ps_cmt.executeUpdate();
				c_acc_type++;
			}

			int total = c_param + c_country + c_cst_type + c_acc_type;
			logger.info("<===== SUCCESS REFRESH PARAMETER =====> DETAIL : Param[{}] + Country[{}] + CstType[{}] + AccType[{}] = {} by {}", new Object[] {c_param, c_country, c_cst_type, c_acc_type, total, user});
			
			return true;
		} catch (SQLException e) {
			logger.error(LogEvent.GENERAL, e);
			return false;
		} finally {
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (stmt!=null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.STATEMENT__CLOSE, e);
				}
			}
			if (ps_cmt!=null) {
				try {
					ps_cmt.close();
				} catch (SQLException e) {
					logger.error(LogEvent.PREP_STMT__CLOSE, e);
				}
			}
			if (dwhConn != null) {
				try {
					dwhConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
			if (cmtConn != null) {
				try {
					cmtConn.close();
				} catch (SQLException e) {
					logger.error(LogEvent.CONNECTION__CLOSE, e);
				}
			}
		}
	}
	
	/*
	 * PURPOSE : cek apakah table TEMP_CIF, TEMP_ACCOUNT, TEMP_ADDRESS di CMT masih kosong
	 * CLUE : if these tables still empty, then return true
	 */
//	public Boolean isNeedInitial() {
//		Connection cmtConn = null;
//		Statement stmt = null;
//		long cif=0, account=0, address=0;
//		try {
//			cmtConn = CustodyDataSource.getConnection();
//			
//			stmt = cmtConn.createStatement();
//			ResultSet rs = stmt.executeQuery("SELECT * FROM " +
//					"(SELECT COUNT(1) AS cif FROM " + Constant.CMT_CIF + ") AS A, " +
//					"(SELECT COUNT(1) AS account FROM " + Constant.CMT_ACCOUNT + ") AS B, " +
//					"(SELECT COUNT(1) AS address FROM " + Constant.CMT_ADDRESS + ") AS C");
//			while (rs.next()) {
//				cif = rs.getInt("cif");
//				account = rs.getInt("account");
//				address = rs.getInt("address");
//			}
//						
//		} catch (SQLException e) {
//			logger.error(LogEvent.GENERAL, e);
//		} finally {
//			if (stmt!=null) {
//				try {
//					stmt.close();
//				} catch (SQLException e) {
//					logger.error(LogEvent.STATEMENT__CLOSE, e);
//				}
//			}
//			if (cmtConn != null) {
//				try {
//					cmtConn.close();
//				} catch (SQLException e) {
//					logger.error(LogEvent.CONNECTION__CLOSE, e);
//				}
//			}
//		}
//		
//		if ((cif==0) && (account==0) && (address==0)) {
//			return true;
//		}
//		
//		return false;
//	}
}
